set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_tmp.dm_whole_decide_duty_static_dt
select
     waybill_no                          as waybill_no
    ,route_id                            as route_id                            --路由唯一键
    ,total_days                          as total_days                          --T+时效
    ,first_network_code                  as first_network_code                  --始发网点编码
    ,first_network_name                  as first_network_name                  --始发网点名称
    ,sign_network_code                   as sign_network_code                   --签收网点编码
    ,sign_network_name                   as sign_network_name                   --签收网点名称
    ,last_network_code                   as last_network_code                   --目的网点编码
    ,last_network_name                   as last_network_name                   --目的网点名称
    ,scan_site_plan_no                   as scan_site_plan_no                   --当前站点编号
    ,scan_site_plan_code                 as scan_site_plan_code                 --当前站点编码
    ,scan_site_plan_name                 as scan_site_plan_name                 --当前站点名称
    ,scan_site_plan_type                 as scan_site_plan_type                 --当前站点类型
    ,scan_site_plan_simple_name          as scan_site_plan_simple_name          --当前站点简称
    ,scan_site_plan_send_time            as scan_site_plan_send_time            --当前站点规划发车时间
    ,scan_site_plan_arrival_time         as scan_site_plan_arrival_time         --当前站点规划到车时间
    ,scan_site_plan_last_arrival_time    as scan_site_plan_last_arrival_time    --当前站点规划最晚到车时间
    ,scan_site_plan_last_send_time       as scan_site_plan_last_send_time       --当前站点规划最晚发车时间
    ,plan_diff_time                      as plan_diff_time                      --当前站点与下一站点时间差
    ,plan_last_taking_time               as plan_last_taking_time               --当前站点规划最晚揽收结束时间
    ,plan_deliver_time                   as plan_deliver_time                   --目的网点规划派件时间
    ,plan_sign_time                      as plan_sign_time                      --目的网点规划签收时间
    ,pre_site_plan_send_time             as pre_site_plan_send_time             --上一站点规划发车时间
    ,pre_site_plan_arrival_time          as pre_site_plan_arrival_time          --上一站点规划到车时间
    ,pre_site_plan_last_send_time        as pre_site_plan_last_send_time        --上一站点规划最晚发车时间
    ,pre_site_plan_last_arrival_time     as pre_site_plan_last_arrival_time     --上一站点规划最晚到车时间
    ,next_site_plan_no                   as next_site_plan_no                   --下一站点编号
    ,next_site_plan_code                 as next_site_plan_code                 --下一站点编码
    ,next_site_plan_name                 as next_site_plan_name                 --下一站点名称
    ,next_site_plan_type                 as next_site_plan_type                 --下一站点类型
    ,next_l2_site_code                   as next_l2_site_code                   --下下一站点编码
    ,next_l3_site_code                   as next_l3_site_code                   --下下下一站点编码
    ,next_l4_site_code                   as next_l4_site_code                   --下下下下一站点编码
    ,next_site_plan_send_time            as next_site_plan_send_time            --下一站点规划发车时间
    ,next_site_plan_arrival_time         as next_site_plan_arrival_time         --下一站点规划到车时间
    ,next_site_plan_last_send_time       as next_site_plan_last_send_time       --下一站点规划最晚发车时间
    ,next_site_plan_last_arrival_time    as next_site_plan_last_arrival_time    --下一站点规划最晚到车时间
    ,first_center_plan_send_time         as first_center_plan_send_time         --始发中心规划发车时间
    ,first_center_plan_arrival_time      as first_center_plan_arrival_time      --始发中心规划到车时间
    ,first_center_plan_last_send_time    as first_center_plan_last_send_time    --始发中心规划最晚发车时间
    ,first_center_plan_last_arrival_time as first_center_plan_last_arrival_time --始发中心规划最晚到车时间
    ,scan_site_plan_simple_name          as plan_node                           --规划节点
    ,scan_site_oper_time                 as scan_site_oper_time                 --当前站点操作时长
    ,next_site_plan_oper_time            as next_site_plan_oper_time            --下一站点规划操作时长
    ,scan_site_plan_line_name            as scan_site_plan_line_name            --当前站点规划车线
    ,cainiao_plan_sign_time              as cainiao_plan_sign_time              --达成率菜鸟规划签收时间
    ,ziyou_plan_sign_time                as ziyou_plan_sign_time                --达成率自由规划签收时间
    ,pre_site_code                       as pre_site_code                       --上上一站编码
    ,pre_l2_site_code                    as pre_l2_site_code                    --上上一站编码
    ,pre_l3_site_code                    as pre_l3_site_code                    --上上上一站编码
    ,pre_l4_site_code                    as pre_l4_site_code                    --上上上上一站编码
    ,pre_site_plan_type                  as pre_site_plan_type                  --上一站点类型
    ,dt                                  as dt
from(
    select
        *
        ,first_value(case when scan_site_plan_type = 4 then scan_site_plan_last_send_time    end) over(partition by waybill_no,route_id order by scan_site_plan_type,scan_site_plan_no) as first_center_plan_last_send_time    --始发中心规划最晚发车时间
        ,first_value(case when scan_site_plan_type = 4 then scan_site_plan_last_arrival_time end) over(partition by waybill_no,route_id order by scan_site_plan_type,scan_site_plan_no) as first_center_plan_last_arrival_time --始发中心规划最晚到车时间
        ,lag(scan_site_plan_last_arrival_time ) over(partition by waybill_no,route_id order by scan_site_plan_no) as pre_site_plan_last_arrival_time  --上一站点规划最晚到车时间
        ,lead(scan_site_plan_last_arrival_time) over(partition by waybill_no,route_id order by scan_site_plan_no) as next_site_plan_last_arrival_time --下一站点规划最晚到车时间
        ,concat(cast((unix_timestamp(next_site_plan_arrival_time) - unix_timestamp(scan_site_plan_send_time)) /60 /60 as decimal(15,2)),'H') as plan_diff_time --当前站点与下一站规划时间差
    from(
        select
             *              --当前站点规划最晚到车时间
            ,lag(scan_site_plan_send_time      ) over(partition by waybill_no,route_id order by scan_site_plan_no) as pre_site_plan_send_time       --上一站点规划发车时间
            ,lag(scan_site_plan_arrival_time   ) over(partition by waybill_no,route_id order by scan_site_plan_no) as pre_site_plan_arrival_time    --上一站点规划到车时间
            ,lag(scan_site_plan_last_send_time ) over(partition by waybill_no,route_id order by scan_site_plan_no) as pre_site_plan_last_send_time  --上一站点规划最晚发车时间
            ,lead(scan_site_plan_send_time     ) over(partition by waybill_no,route_id order by scan_site_plan_no) as next_site_plan_send_time      --下一站点规划发车时间
            ,lead(scan_site_plan_arrival_time  ) over(partition by waybill_no,route_id order by scan_site_plan_no) as next_site_plan_arrival_time   --下一站点规划到车时间
            ,lead(scan_site_plan_last_send_time) over(partition by waybill_no,route_id order by scan_site_plan_no) as next_site_plan_last_send_time --下一站点规划最晚发车时间
            ,lead(scan_site_oper_time          ) over(partition by waybill_no,route_id order by scan_site_plan_no) as next_site_plan_oper_time      --下一站点规划操作时长
            ,first_value(case when scan_site_plan_type = 4 then scan_site_plan_send_time    end) over(partition by waybill_no,route_id order by scan_site_plan_type,scan_site_plan_no) as first_center_plan_send_time     --始发中心规划发车时间
            ,first_value(case when scan_site_plan_type = 4 then scan_site_plan_arrival_time end) over(partition by waybill_no,route_id order by scan_site_plan_type,scan_site_plan_no) as first_center_plan_arrival_time  --始发中心规划到车时间
            ,lag(scan_site_plan_type) over(partition by waybill_no,route_id order by scan_site_plan_no) as pre_site_plan_type  --上一站点类型
        from (
            select
                 wide.waybill_no                  as waybill_no
                ,wide.first_network_code          as first_network_code             --始发网点编码
                ,wide.first_network_name          as first_network_name             --始发网点名称
                ,wide.sign_network_code           as sign_network_code              --签收网点编码
                ,wide.sign_network_name           as sign_network_name              --签收网点名称
                ,wide.last_network_code           as last_network_code              --目的网点编码
                ,wide.last_network_name           as last_network_name              --目的网点名称
                ,route.route_id                   as route_id                       --路由唯一键
                ,route.scan_site_no               as scan_site_plan_no              --当前站点编号
                ,route.scan_site_code             as scan_site_plan_code            --当前站点编码
                ,route.scan_site_name             as scan_site_plan_name            --当前站点名称
                ,route.scan_site_type             as scan_site_plan_type            --当前站点类型
                ,route.scan_site_simple_name      as scan_site_plan_simple_name     --当前站点简称
                ,route.next_site_no               as next_site_plan_no              --下一站点编号
                ,route.next_site_code             as next_site_plan_code            --下一站点编码
                ,route.next_site_name             as next_site_plan_name            --下一站点名称
                ,route.next_site_type             as next_site_plan_type            --下一站点类型
                ,route.next_l2_site_code          as next_l2_site_code              --下下一站点编码
                ,route.next_l3_site_code          as next_l3_site_code              --下下下一站点编码
                ,route.next_l4_site_code          as next_l4_site_code              --下下下下一站点编码
                ,route.total_days                 as total_days                     --T+时效
                ,route.operate_time               as scan_site_oper_time            --当前站点操作时长
                ,route.scan_site_line_name        as scan_site_plan_line_name       --车线
                ,concat(date_add(wide.taking_date,send_span_days         + if_taking_off),' ',route.plan_send_time        ,':00') as scan_site_plan_send_time         --当前站点规划发车时间
                ,concat(date_add(wide.taking_date,arrival_span_days      + if_taking_off),' ',route.plan_arrival_time     ,':00') as scan_site_plan_arrival_time      --当前站点规划到车时间
                ,concat(date_add(wide.taking_date,send_span_days         + if_taking_off),' ',route.plan_last_send_time   ,':00') as scan_site_plan_last_send_time    --当前站点规划最晚发车时间
                ,concat(date_add(wide.taking_date,last_arrival_span_days + if_taking_off),' ',route.plan_last_arrival_time,':00') as scan_site_plan_last_arrival_time --当前站点规划发车时间
                ,concat(date_add(wide.taking_date,send_span_days         + if_taking_off),' ',route.plan_deliver_time           ) as plan_deliver_time                --目的网点规划派件时间
                ,concat(date_add(wide.taking_date,total_days             + if_taking_off),' ',route.plan_sign_time              ) as plan_sign_time                   --目的网点规划签收时间
                ,concat(to_date(wide.taking_date) ,' ',route.last_taking_time) as plan_last_taking_time            --当前站点规划最晚揽收结束时间
                ,wide.cainiao_plan_sign_time  --达成率菜鸟规划签收时间
                ,wide.ziyou_plan_sign_time    --达成率自由规划签收时间
                ,route.pre_site_code          --上一站编码
                ,route.pre_l2_site_code       --上上一站编码
                ,route.pre_l3_site_code       --上上上一站编码
                ,route.pre_l4_site_code       --上上上上一站编码
                ,wide.dt as dt
            from (
                select
                     waybill_no
                    ,route_id
                    ,taking_date
                    ,first_network_code
                    ,first_network_name
                    ,sign_network_code
                    ,sign_network_name
                    ,last_network_code
                    ,last_network_name
                    ,cainiao_plan_sign_time  --达成率菜鸟规划签收时间
                    ,ziyou_plan_sign_time    --达成率自由规划签收时间
                    ,if_taking_off           --截单后揽收
                    ,dt
                from(
                    select
                          waybill_no
                         ,first_value(route_id) over(partition by waybill_no order by scan_site_no) as route_id --路由唯一键
                         ,taking_date
                         ,first_network_code
                         ,first_network_name
                         ,sign_network_code
                         ,sign_network_name
                         ,last_network_code
                         ,last_network_name
                         ,cainiao_plan_sign_time  --达成率菜鸟规划签收时间
                         ,ziyou_plan_sign_time    --达成率自由规划签收时间
                         ,if_taking_off           --是否截单后揽收
                         ,dt
                    from jms_tmp.dm_whole_decide_duty_dynamic_dt
                    where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
                    and route_id is not null
                ) a group by waybill_no
                            ,route_id
                            ,taking_date
                            ,first_network_code
                            ,first_network_name
                            ,sign_network_code
                            ,sign_network_name
                            ,last_network_code
                            ,last_network_name
                            ,cainiao_plan_sign_time  --达成率菜鸟规划签收时间
                            ,ziyou_plan_sign_time    --达成率自由规划签收时间
                            ,if_taking_off
                            ,dt
            ) wide
            left join (
                select
                     route.*
                    ,nvl(dim.operate_time,0) as operate_time
                from jms_dm.dm_route_whole_step_dt route
                left join (
                    select
                         code
                        ,name
                        ,nvl(dim.operate_time,0) as operate_time
                    from jms_dim.dim_tmsnew_vehicle_line dim
                    where is_enable = 1
                ) dim on replace(route.scan_site_line_name,'(计重)','') = dim.name
                where route.dt between date_sub('{{ execution_date | cst_ds }}',21) and '{{ execution_date | cst_ds }}'
            ) route on route.route_id = wide.route_id  --站点相等
                   and route.first_network_code = wide.first_network_code  --始发网点
                   and route.last_network_code = wide.last_network_code    --目的网点
        ) a
    ) a
) a
distribute by pmod(hash(rand()),60);